import mysql.connector
import os
from dotenv import load_dotenv


def initialize_database(db_name="aidemo", db_user="root", db_password="password", db_host="localhost", db_port="3306", db_dir="data"):
    """
    Initializes a MySQL database with 'products', 'orders', and 'order_details' tables.

    Args:
        db_name (str): The name of the database.
        db_user (str): The MySQL username.
        db_password (str): The MySQL password.
        db_host (str): The MySQL host.
        db_port (str): The MySQL port.
        db_dir (str): The directory where the database file would be stored (not used in MySQL).
    """

    conn = None  # Initialize conn to None
    try:
        # Connect to MySQL server
        conn = mysql.connector.connect(
            host=db_host,
            port=db_port,
            user=db_user,
            password=db_password
        )
        cursor = conn.cursor()

        # Create the database if it doesn't exist
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name};")
        cursor.execute(f"USE {db_name};")

        # --- Create 'products' table ---
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                product_id VARCHAR(255) PRIMARY KEY,
                product_name VARCHAR(255) NOT NULL,
                product_type VARCHAR(255),
                specification VARCHAR(255),
                price DECIMAL(10, 2) NOT NULL,
                stock_quantity INT NOT NULL,
                description TEXT
            );
        """)

        # Insert sample data into 'products'
        cursor.execute("INSERT IGNORE INTO products VALUES (%s, %s, %s, %s, %s, %s, %s)",
                       ('001', '五粮液', '白酒', '1箱*4瓶', 100.0, 800, '普五八代'))  # Changed price to 100.0 and stock to 800
        cursor.execute("INSERT IGNORE INTO products VALUES (%s, %s, %s, %s, %s, %s, %s)",
                       ('002', '茅台', '白酒', '1箱*6瓶', 200.0, 500, '飞天茅台'))
        cursor.execute("INSERT IGNORE INTO products VALUES (%s, %s, %s, %s, %s, %s, %s)",
                       ('003', '剑南春', '白酒', '1箱*4瓶', 80.0, 1200, '东方红'))

        # --- Create 'orders' table ---
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id VARCHAR(255) PRIMARY KEY,
                purchaser VARCHAR(255) NOT NULL,
                total_amount DECIMAL(10, 2) NOT NULL,
                order_time DATETIME NOT NULL,
                expected_delivery_time VARCHAR(45),
                delivery_time DATETIME,
                is_settled TINYINT NOT NULL,
                settlement_time DATETIME
            );
        """)

        # Insert sample data into 'orders'
        cursor.execute("INSERT IGNORE INTO orders VALUES (%s, %s, %s, %s, %s, %s)",
                       ('20250605001', '上海白金酒店管理有限公司', 56400.0, '2025-06-05 10:00:00', '2025-06-05 18:00:00', 1))
        cursor.execute("INSERT IGNORE INTO orders VALUES (%s, %s, %s, %s, %s, %s)",
                       ('20250605002', '北京四季酒店', 30000.0, '2025-06-05 11:30:00', None, 0))
        cursor.execute("INSERT IGNORE INTO orders VALUES (%s, %s, %s, %s, %s, %s)",
                       ('20250605003', '广州喜来登酒店', 15000.0, '2025-06-05 12:00:00', None, 0))

        # --- Create 'order_details' table ---
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS order_details (
                detail_id VARCHAR(255) PRIMARY KEY,
                order_id VARCHAR(255) NOT NULL,
                product_id VARCHAR(255) NOT NULL,
                quantity INT NOT NULL,
                price DECIMAL(10, 2) NOT NULL,
                amount DECIMAL(10, 2) NOT NULL,
                FOREIGN KEY (order_id) REFERENCES orders(order_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            );
        """)

        # Insert sample data into 'order_details'
        cursor.execute("INSERT IGNORE INTO order_details VALUES (%s, %s, %s, %s, %s, %s)",
                       ('D001', '20250605001', '001', 500, 100.0, 50000.0))
        cursor.execute("INSERT IGNORE INTO order_details VALUES (%s, %s, %s, %s, %s, %s)",
                       ('D002', '20250605001', '002', 20, 200.0, 4000.0))
        cursor.execute("INSERT IGNORE INTO order_details VALUES (%s, %s, %s, %s, %s, %s)",
                       ('D003', '20250605001', '003', 30, 80.0, 2400.0))
        cursor.execute("INSERT IGNORE INTO order_details VALUES (%s, %s, %s, %s, %s, %s)",
                       ('D004', '20250605002', '001', 200, 100.0, 20000.0))
        cursor.execute("INSERT IGNORE INTO order_details VALUES (%s, %s, %s, %s, %s, %s)",
                       ('D005', '20250605002', '002', 50, 200.0, 10000.0))
        cursor.execute("INSERT IGNORE INTO order_details VALUES (%s, %s, %s, %s, %s, %s)",
                       ('D006', '20250605003', '003', 150, 80.0, 12000.0))
        cursor.execute("INSERT IGNORE INTO order_details VALUES (%s, %s, %s, %s, %s, %s)",
                       ('D007', '20250605003', '001', 30, 100.0, 3000.0))

        conn.commit()
        print(f"Database '{db_name}' initialized successfully.")
        print("Tables 'products', 'orders', and 'order_details' created and populated with sample data.")

    except mysql.connector.Error as e:
        print(f"An error occurred: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    
    # 加载 .env 文件
    load_dotenv()

    dbhost = os.getenv("DB_HOST", "localhost")
    print(f"Connecting to database at {dbhost}...")
    dbport = os.getenv("DB_PORT", "3306")
    dbuser = os.getenv("DB_USER", "root")
    dbpassword = os.getenv("DB_PASSWORD", "password")
    dbname = os.getenv("DB_NAME", "aidemo")
    
    initialize_database(db_name=dbname, db_user=dbuser, db_password=dbpassword, db_host=dbhost, db_port=dbport)